package com.dudu.dao;

import java.sql.Connection;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.bean.PoolAbilityInfoBean;
import com.dudu.bean.PoolCaseBean;
import com.dudu.model.PoolCase;
import com.dudu.utils.EnumUtility;

import core.CoreDao;
import core.db.DBConnectionUtil;
/**
 * 号码池相应查询工具
 * @author weidong
 *
 */
public class PoolNumberUtilsDao {
	private static final Logger log =  core.util.Logger.getLogger(PoolNumberUtilsDao.class.getSimpleName());
	
	/**
	 * 根据应用key、账号标识和能力标识，查询对应账号所使用的电话号码
	 * @param dao 数据库连接
	 * @param appKey 应用key
	 * @param custAccount 账号标识
	 * @param abilityType 能力标识
	 * @param displayNbr 显示号码
	 * @param usePlatNbr 强制使用平台号
	 * @param number_vest 地区
	 * @return
	 * @throws Exception
	 */
	public List<PoolAbilityInfoBean> queryPoolNumberInfo(CoreDao dao, String appKey, String custAccount, String abilityType,String displayNbr, String usePlatNbr,String number_vest) throws Exception{

		//数据库连接
		Connection conn = null;
		//最后需返回的
		List<PoolAbilityInfoBean> result = null;
		
		try {
			//获取数据库连接
			conn = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ti.id, ti.pool_id, ti.ability_type, ti.display_number, ti.charge_number, ti.charge_key, ti.reserve_column1, ti.reserve_column2, ti.comment_info, ti.limit_num, ti.create_time, ti.update_time, ti.create_user, ti.update_user ");
			sql.append(" FROM t_pool_ability_info ti,t_pool_account_rel tr ");
			if(StringUtils.isNotBlank(usePlatNbr) || StringUtils.isNotBlank(number_vest)){
				sql.append(" , t_pool_number n ");
			}
			sql.append(" WHERE ti.id=tr.pool_num_id ");
			if("1".equalsIgnoreCase(usePlatNbr)){ //使用平台号
				sql.append(" AND ti.pool_id=n.id ");
				sql.append(" AND n.number_source='external' ");
			}else if("2".equalsIgnoreCase(usePlatNbr)){ //使用用户认证号码
				sql.append(" AND ti.pool_id=n.id ");
				sql.append(" AND n.number_source='custom' ");
			}
			if(StringUtils.isNotBlank(number_vest)){
			    sql.append("AND ti.pool_id=n.id");
			    sql.append(" AND n.number_vest='"+number_vest+"' ");
			}
			sql.append(" AND tr.app_key=? AND tr.cust_account=? AND ti.ability_type=? ");
			if(EnumUtility.isNotBlank(displayNbr)){
				sql.append(" and ti.display_number='"+displayNbr+"' ");
			}
			log.info("queryPoolNumberInfo SQL="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(conn, sql.toString(), new Object[] { appKey, custAccount, abilityType });
			//将json类型转化为PoolAbilityInfo
			if (list != null) {
				result = dao.covertToBean(list, PoolAbilityInfoBean.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, conn);
		}
		return result;
	}
	
	/**
	 * 查询某个账号id的号码能力的当天使用情况
	 * @param dao
	 * @param appKey 应用key
	 * @param custAccount 账号标识
	 * @param poolAbilityId 号码的能力id
	 * @param data_time 查询的时间（天） 如：2016-03-04
	 * @return
	 */
	public PoolCase queryNumTimeByAcc(CoreDao dao, String appKey, String custAccount, String poolAbilityId, String data_time) throws Exception{
		//数据库连接
		Connection conn = null;
		//最后需返回的
		PoolCase result = null;
		
		try {
			//获取数据库连接
			conn = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tc.id,tc.data_time, tc.pool_ability_id, SUM(tc.num_time) num_time, tc.app_key, tc.cust_account, '2016-06-03' AS create_time, tc.comment_info ");
			sql.append(" FROM (SELECT pool_id FROM t_pool_ability_info WHERE id=?) tm,t_pool_case tc,t_pool_ability_info ti ");
			sql.append(" WHERE tm.pool_id=ti.pool_id AND tc.pool_ability_id=ti.id AND tc.app_key=? AND tc.cust_account=? AND tc.data_time=? ");
			sql.append(" GROUP BY ti.pool_id");
		
			log.info("queryNumTimeByAcc SQL="+sql.toString());
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(conn, sql.toString(), new Object[] { poolAbilityId, appKey, custAccount, data_time });
			//将json类型转化为PoolAbilityInfo
			if (list != null) {
				List<PoolCase> resultlist=null;
				resultlist = dao.covertToBean(list, PoolCase.class);
				//获取第一条数据
				if(!resultlist.isEmpty()&&resultlist.size()>0){
					result=resultlist.get(0);
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, conn);
		}
		return result;
	}
	
	/**
	 * 查询多个账号id的号码能力的当天使用情况
	 * @param dao
	 * @param appKey 应用key
	 * @param custAccount 账号标识
	 * @param poolAbilityId 号码的能力id
	 * @param data_time 查询的时间（天） 如：2016-03-04
	 * @return
	 */
	public List<PoolCase> queryNumTimeByAccs(CoreDao dao, String appKey, String custAccount, String poolAbilityId, String data_time) throws Exception{
		//数据库连接
		Connection conn = null;
		//最后需返回的
		List<PoolCase> result = null;
		
		try {
			//获取数据库连接
			conn = DBConnectionUtil.getDBConnection("emate1");
			//穿入参数
			String poolAbilityIdv = "";
			//判断参数是否为空
			if(StringUtils.isNotBlank(poolAbilityId)){
				//拆分字符串
				String[] poolAbilityIds=poolAbilityId.split(",");
				for(int i=0;i<poolAbilityIds.length;i++){
					if(i==0){
						poolAbilityIdv="'"+poolAbilityIds[i]+"'";
					}else{
						poolAbilityIdv=poolAbilityIdv+",'"+poolAbilityIds[i]+"'";
					}
				}
			}else{
				return null;
			}
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tc.data_time, tc.pool_ability_id, SUM(tc.num_time) num_time, tc.app_key, tc.cust_account, tc.comment_info ");
			sql.append(" FROM (SELECT pool_id FROM t_pool_ability_info WHERE id IN("+poolAbilityIdv+")) tm,t_pool_case tc,t_pool_ability_info ti ");
			sql.append(" WHERE tm.pool_id=ti.pool_id AND tc.pool_ability_id=ti.id AND tc.app_key=? AND tc.cust_account=? AND tc.data_time=? ");
			sql.append(" GROUP BY ti.pool_id");
		
			log.info("queryNumTimeByAcc SQL="+sql.toString());
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(conn, sql.toString(), new Object[] { appKey, custAccount, data_time });
			//将json类型转化为PoolAbilityInfo
			if (list != null) {
				result = dao.covertToBean(list, PoolCase.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, conn);
		}
		return result;
	}
	/**
	 * 新建PoolCase信息
	 * @param dao
	 * @param PoolCase 成功返回0，失败返回-1
	 * @return
	 */
	public int insertPoolCase(CoreDao dao, PoolCase poolCase) throws Exception{
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer("INSERT INTO t_pool_case(data_time,pool_ability_id,num_time,app_key,cust_account,create_time,comment_info) ");
			sql.append("VALUES(?, ?, ?, ?, ?, sysdate(), ?)");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						poolCase.getData_time(),
						poolCase.getPool_ability_id(),
						1,
						poolCase.getApp_key(),
						poolCase.getCust_account(),
						poolCase.getComment_info()
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 更新PoolCase使用次数
	 * @param dao
	 * @param poolCaseId 数据唯一性标识 
	 * @return 成功返回0，失败返回-1
	 */
	public int updatePoolCaseNumTime(CoreDao dao, String poolCaseId) throws Exception{
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer("UPDATE t_pool_case SET num_time=num_time+1 ");
			sql.append("WHERE id=?");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						poolCaseId
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 根据账号标识、号码能力和日期，查询对应账号所使用的号码能力的使用量
	 * @param dao
	 * @param appKey 应用key
	 * @param custAccount 账号标识
	 * @param abilityType
	 * @return
	 */
	public List<PoolCaseBean> queryPoolCaseByAcc(CoreDao dao, String appKey, String custAccount, String abilityType, String data_time) throws Exception{

		//数据库连接
		Connection conn = null;
		//最后需返回的
		List<PoolCaseBean> result = null;
		
		try {
			//获取数据库连接
			conn = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tc.id, tc.data_time, tc.pool_ability_id, tc.num_time, tc.app_key, tc.cust_account,  tc.create_time, tc.comment_info ");
			sql.append("FROM t_pool_ability_info ti,t_pool_account_rel tr,t_pool_case tc ");
			sql.append("WHERE ti.id=tr.pool_num_id AND tc.pool_ability_id=ti.id AND tc.app_key=? AND tc.cust_account=? AND ti.ability_type=? AND tc.data_time=? ");
			sql.append("ORDER BY tc.num_time");
			log.info("queryPoolCaseByAcc SQL="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(conn, sql.toString(), new Object[] { appKey, custAccount, abilityType, data_time });
			//将json类型转化为PoolCase
			if (list != null) {
				result = dao.covertToBean(list, PoolCaseBean.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
		} finally {
			DBConnectionUtil.close(null, null, conn);
		}
		return result;
	}
}
